﻿/*
 * Catalyst - http://catalystobjects.com
 * 
 * New BSD License (BSD)
 * 
 * Copyright (c) 2009, Ashley Brener
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
 * 
 * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
 * 
 * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
 * 
 * Neither the name of Midlet Earth nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web;
using System.Web.Caching;
using SubSonic.DataProviders;
using SubSonic.Query;
using System.Security.Principal;
using System.Threading;
using System.Data.SqlClient;

namespace Catalyst.Caching
{
	public class AspNetSqlCacheEnforcer
	{
		private static List<string> enforcedDatabases;
		private const string triggerSuffix = "_AspNet_SqlCacheNotification_Trigger";
		
		private readonly IDatabase _db;

		public AspNetSqlCacheEnforcer(IDatabase db)
		{
			_db = db;
			
			if (HttpContext.Current != null && _db.SqlCacheTables != null && _db.SqlCacheTables.Count > 0)
			{
				enforceCacheTables();
			}
		}

		public bool CheckIfCachedTable(string TableName)
		{
			foreach (string enabledTable in _db.SqlCacheTables)
			{
				if (enabledTable == TableName)
				{
					return true;
				}
			}

			return false;
		}

		private void enforceCacheTables()
		{
			if (enforcedDatabases == null || !enforcedDatabases.Contains(_db.Provider.ConnectionString))
			{
					string existingTable = new Select(_db.Provider, "name")
					.From("sysobjects")
					.Where("name").IsEqualTo("AspNet_SqlCacheTablesForChangeNotification")
					.ExecuteScalar<string>();

					if (String.IsNullOrEmpty(existingTable))
					{
						// Enable database for SqlCacheNotifications
						SqlCacheDependencyAdmin.EnableNotifications(_db.Provider.ConnectionString);
					}

					List<string> notificationTables = new List<string>(SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(_db.Provider.ConnectionString));

					List<string> triggers = new List<string>();

					IDataReader reader = new Select(_db.Provider, "name")
					.From("sysobjects")
					.Where("xtype").IsEqualTo("TR")
					.And("name").Like("%" + triggerSuffix)
					.ExecuteReader();

					while (reader.Read())
					{
						triggers.Add(reader["name"].ToString());
					}

					reader.Close();

					// Enable SqlCacheDependency if table does not exist in AspNet_SqlCacheTablesForChangeNotification
					// table or no trigger exists on table				
					foreach (string tableName in _db.SqlCacheTables)
					{
						if (!notificationTables.Contains(tableName) || !triggers.Contains(tableName + triggerSuffix))
						{
							try
							{
								SqlCacheDependencyAdmin.EnableTableForNotifications(_db.Provider.ConnectionString, tableName);
							}
							catch (Exception ex)
							{
								throw new Exception(ex.Message + "\r\n\r\nConnectionString: " + _db.Provider.ConnectionString + "\r\n\r\nTable: " + tableName);
							}
						}
					}

					// Disable SqlCacheDependency if table in AspNet_SqlCacheTablesForChangeNotification 
					// does not exist in SqlCacheTables
					foreach (string tableName in notificationTables)
					{
						if (!_db.SqlCacheTables.Contains(tableName))
						{
							SqlCacheDependencyAdmin.DisableTableForNotifications(_db.Provider.ConnectionString, tableName);
						}
					}

					// Disable SqlCacheDependency if table has trigger but does not exist in SqlCacheTables
					foreach (string trigger in triggers)
					{
						string tableName = trigger.Replace(triggerSuffix, "");

						if (!_db.SqlCacheTables.Contains(tableName))
						{
							SqlCacheDependencyAdmin.DisableTableForNotifications(_db.Provider.ConnectionString, tableName);
						}
					}

					if (HttpContext.Current == null)
					{
						setupSqlDependencyPermissions();

					//object obj = new Select(_db.Provider, "name")
					//.From("sysobjects")
					//.Where("xtype").IsEqualTo("SQ")
					//.And("name").IsEqualTo("TableChangedQueue")
					//.ExecuteScalar();

					//if (obj == null)
					//{
						//StringBuilder sql = new StringBuilder();
						////sql.AppendLine("GRANT CREATE PROCEDURE TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT CREATE SERVICE TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT CREATE QUEUE TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT CONTROL ON SCHEMA::[dbo] TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT IMPERSONATE ON USER::DBO TO [NT AUTHORITY\\NETWORK SERVICE];");
						////sql.AppendLine("GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [NT AUTHORITY\\NETWORK SERVICE];");

						//sql.AppendLine(String.Format("ALTER DATABASE {0} SET NEW_BROKER;", _db.Name));

						//sql.AppendLine(String.Format("CREATE QUEUE {0}ChangedQueue;", tab));
						//sql.AppendLine("CREATE SERVICE TableChangedService ON QUEUE TableChangedQueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);");

						////sql.AppendLine(String.Format("ALTER DATABASE {0} SET NEW_BROKER;", databaseName));

						//_db.Provider.ExecuteQuery(new QueryCommand(sql.ToString(), _db.Provider));
					//}
				}

				if (enforcedDatabases == null)
				{
					enforcedDatabases = new List<string>();
				}

				enforcedDatabases.Add(_db.Provider.ConnectionString);
			}
		}

		private void setupSqlDependencyPermissions()
		{
			string sql = null;

			//object obj = new Select(_db.Provider, "name")
			//.From("sys.databases")
			//.Where("name").IsEqualTo(_db.Name)
			//.And("is_broker_enabled").IsEqualTo(1)
			//.ExecuteScalar();

			//Select databasepropertyex('frisB', 'IsBrokerEnabled') 1 or 0

			//if (obj == null)
			//{
			//    try
			//    {
			//        sql = String.Format("ALTER DATABASE [{0}] SET ENABLE_BROKER", _db.Name);
			//        _db.Provider.ExecuteQuery(new QueryCommand(sql, _db.Provider));
			//    }
			//    catch
			//    {
			//        throw new Exception("Service Broker not enabled in SQL Server.");
			//    }
			//}

			sql = String.Format("SELECT databasepropertyex('{0}', 'IsBrokerEnabled')", _db.Name);
			object obj = null;// _db.Provider.ExecuteScalar(new QueryCommand(sql, _db.Provider));


			if (obj == null || Convert.ToInt32(obj) == 0)
			{
			    try
			    {
			        sql = String.Format("ALTER DATABASE [{0}] SET ENABLE_BROKER", _db.Name);
					//_db.Provider.ExecuteQuery(new QueryCommand(sql, _db.Provider));
			    }
			    catch
			    {
			        throw new Exception("Service Broker not enabled in SQL Server.");
			    }
			}

			sql = String.Format("GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [{0}]", WindowsIdentity.GetCurrent().Name);
			//_db.Provider.ExecuteQuery(new QueryCommand(sql, _db.Provider));

			//sql = String.Format("GRANT SEND ON SERVICE::SqlQueryNotificationService TO [{0}]", WindowsIdentity.GetCurrent().Name);
			//_db.Provider.ExecuteQuery(new QueryCommand(sql, _db.Provider));

			//SqlDependency.Stop(_db.Provider.ConnectionString);
			//SqlDependency.Start(_db.Provider.ConnectionString);












			//StringBuilder sql = new StringBuilder();

			//sql.AppendLine("USE master");

			//// Cleaning up before we start
			//sql.AppendLine("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'startUser')");
			//sql.AppendLine("DROP LOGIN [startUser]");
			//sql.AppendLine("IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'subscribeUser')");
			//sql.AppendLine("DROP LOGIN [subscribeUser]");

			//// Ensuring that Service Broker is enabled 
			////sql.AppendLine(String.Format("ALTER DATABASE [{0}] SET ENABLE_BROKER", _db.Name));
			////sql.AppendLine("GO");

			//// Creating users
			//sql.Append("CREATE LOGIN [startUser] WITH PASSWORD=N'startUser',");
			//sql.AppendLine(String.Format(" DEFAULT_DATABASE=[{0}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF", _db.Name)); 
			////sql.AppendLine("GO");
			
			//sql.Append("CREATE LOGIN [subscribeUser] WITH PASSWORD=N'subscribeUser',");
			//sql.AppendLine(String.Format(" DEFAULT_DATABASE=[{0}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF", _db.Name));
			////sql.AppendLine("GO");

			//// Switching to our database
			////sql.AppendLine(String.Format("use [{0}]", _db.Name));

			//_db.Provider.ExecuteQuery(new QueryCommand(sql.ToString(), _db.Provider));

			//try
			//{
			//    sql = new StringBuilder();

			//    /*
			//     * Creating the users in this database
			//     *
			//     * We're going to create two users. One called startUser. This is the user 
			//     * that is going to have sufficient rights to run SqlDependency.Start.
			//     * The other user is called subscribeUser, and this is the user that is 
			//     * going to actually register for changes on the Users-table created earlier.
			//     * Technically, you're not obligated to make two different users naturally, 
			//     * but I did here anyway to make sure that I know the minimal rights required
			//     * for both operations
			//     *
			//     * Pay attention to the fact that the startUser-user has a default schema set.
			//     * This is critical for SqlDependency.Start to work. Below is explained why.
			//     */
			//    sql.AppendLine("CREATE USER [startUser] FOR LOGIN [startUser]");
			//    sql.AppendLine("WITH DEFAULT_SCHEMA = [startUser]");
			//    //sql.AppendLine("GO");
			//    sql.AppendLine("CREATE USER [subscribeUser] FOR LOGIN [subscribeUser]");
			//    //sql.AppendLine("GO");

			//    _db.Provider.ExecuteQuery(new QueryCommand(sql.ToString(), _db.Provider));

			//}
			//catch { }

			//try
			//{
			//    sql = new StringBuilder();

			//    /*
			//     * Creating the schema
			//     *
			//     * It is vital that we create a schema specifically for startUser and that we
			//     * make this user the owner of this schema. We also need to make sure that 
			//     * the default schema of this user is set to this new schema (we have done 
			//     * this earlier)
			//     *
			//     * If we wouldn't do this, then SqlDependency.Start would attempt to create 
			//     * some queues and stored procedures in the user's default schema which is
			//     * dbo. This would fail since startUser does not have sufficient rights to 
			//     * control the dbo-schema. Since we want to know the minimum rights startUser
			//     * needs to run SqlDependency.Start, we don't want to give him dbo priviliges.
			//     * Creating a separate schema ensures that SqlDependency.Start can create the
			//     * necessary objects inside this startUser schema without compromising 
			//     * security.
			//     */
			//    sql.AppendLine("CREATE SCHEMA [startUser] AUTHORIZATION [startUser]");
			//    //sql.AppendLine("GO");

			//    _db.Provider.ExecuteQuery(new QueryCommand(sql.ToString(), _db.Provider));

			//}
			//catch { }

			///*
			// * Creating two new roles. We're not going to set the necessary permissions 
			// * on the user-accounts, but we're going to set them on these two new roles.
			// * At the end of this script, we're simply going to make our two users 
			// * members of these roles.
			// */

			//spAddRole("sql_dependency_subscriber");
			//spAddRole("sql_dependency_starter");

			//sql = new StringBuilder();

			//// Permissions needed for [sql_dependency_starter]
			//sql.AppendLine("GRANT CREATE PROCEDURE TO [sql_dependency_starter]"); 
			//sql.AppendLine("GRANT CREATE QUEUE TO [sql_dependency_starter]");
			//sql.AppendLine("GRANT CREATE SERVICE TO [sql_dependency_starter]");
			//sql.AppendLine("GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [sql_dependency_starter]");
			//sql.AppendLine("GRANT VIEW DEFINITION TO [sql_dependency_starter]");

			//// Permissions needed for [sql_dependency_subscriber] 
			//sql.AppendLine("GRANT SELECT TO [sql_dependency_subscriber]");
			//sql.AppendLine("GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]"); 
			//sql.AppendLine("GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]"); 
			//sql.AppendLine("GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [sql_dependency_subscriber]");
			
			//_db.Provider.ExecuteQuery(new QueryCommand(sql.ToString(), _db.Provider));

			//// Making sure that my users are member of the correct role.
			//spAddRoleMember("sql_dependency_starter", "startUser");
			//spAddRoleMember("sql_dependency_subscriber", "subscribeUser");
		}

		//private void spAddRole(string roleName)
		//{
		//    try
		//    {
		//        QueryCommand cmd = new QueryCommand("sp_addrole", _db.Provider);
		//        cmd.CommandType = CommandType.StoredProcedure;
		//        cmd.Parameters.Add("@rolename", roleName, DbType.String, ParameterDirection.Input);

		//        _db.Provider.ExecuteQuery(cmd);
		//    }
		//    catch { }
		//}

		//private void spAddRoleMember(string roleName, string memberName)
		//{
		//    try
		//    {
		//        QueryCommand cmd = new QueryCommand("sp_addrolemember", _db.Provider);
		//        cmd.CommandType = CommandType.StoredProcedure;
		//        cmd.Parameters.Add("@rolename", roleName, DbType.String, ParameterDirection.Input);
		//        cmd.Parameters.Add("@membername", memberName, DbType.String, ParameterDirection.Input);

		//        _db.Provider.ExecuteQuery(cmd);
		//    }
		//    catch { }
		//}
	}
}
